package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;

public class DBUtil {
    public static Connection get_Conn() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "192.168.1.230";
        String params = "characterEncoding=utf-8&useSSL=false&";
        int port = 13306;
        String database = "wewoolgs";
        String user = "test";
        String pwd = "Wewoo#test#2021";
        String Url = String.format("jdbc:mysql://%s:%s/%s?%s", url, port, database, params);
        return DriverManager.getConnection(Url, user, pwd);
    }

    /**
     * @param //要执行的sql语句
     */
//    public static void execute(String sql) throws SQLException {
//        try (Connection conn = get_Conn();
//             PreparedStatement ps = conn.prepareStatement(sql)) {
//            ps.execute();
//        } catch (SQLException | ClassNotFoundException e) {
//            e.printStackTrace();
//        }
//    }
    // 关闭数据库连接
    public static void close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 关闭Statement对象
    public static void close(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    // 关闭结果集
    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    // 关闭jdbc对象
    public static void close(Connection conn, Statement st, ResultSet rs) {
        close(rs);
        close(st);
        close(conn);
    }
    // 查询一条记录

    /**
     *
     * @param sql  语句
     * @return  查询数据
     * @throws SQLException 捕获异常
     * @throws ClassNotFoundException  捕获异常
     */
    public static Map<String, Object> getOne(String sql) throws SQLException, ClassNotFoundException {
        Map<String, Object> result = new HashMap<String, Object>();
        Connection conn = get_Conn();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            // 通过元数据对象可以获取到查询结果的列数、字段名
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String columnLabel = metaData.getColumnLabel(i);
                Object value = rs.getObject(i);
                result.put(columnLabel, value);
            }
        }
        close(conn);
        return result;
    }
//    public static boolean StringIsDecimals(String str){
//        return Pattern.matches("^((-|\\+)?(([1-9]\\d*)|0)(\\.\\d*)?)|0$", str);
//    }

    /**
     *
     * @param
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String sql = "select id  from lgs_collection_bill where collection_number='SK210400603'";
        String sql_cny="select total_receive_cny  from lgs_collection_bill where collection_number='SK210400603'";
        String sql_usd="select total_receive_usd  from lgs_collection_bill where collection_number='SK210400603'";
        Map<String, Object> result_id = getOne(sql);
        Map<String ,Object> result_cny=getOne(sql_cny);
        Map<String ,Object> result_usd=getOne(sql_usd);
        System.out.println("id==" + result_id.get("id"));
        Object data_cny=result_cny.get("total_receive_cny");
        Object data_usd=result_usd.get("total_receive_usd");
        float cny=Float.parseFloat(String.valueOf(data_cny));
        float usd=Float.parseFloat(String.valueOf(data_usd));
        System.out.println(usd);
        System.out.println(cny);
    }
}


